package com.ruoyi.system.service.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.ruoyi.common.annotation.DataSource;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.domain.ChinaPersons;
import com.ruoyi.system.domain.SysFields;
import com.ruoyi.system.domain.SysFonds;
import com.ruoyi.system.mapper.ArTableRelationMapper;
import com.ruoyi.system.mapper.SysFieldsMapper;
import com.ruoyi.system.service.ISysDeptService;
import com.ruoyi.system.service.ISysFieldsService;
import com.ruoyi.system.service.ISysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.SysTableMapper;
import com.ruoyi.system.domain.SysTable;
import com.ruoyi.system.service.ISysTableService;
import org.springframework.transaction.annotation.Transactional;

/**
 * 档案设置Service业务层处理
 *
 * @author ruoyi_hsl
 * @date 2022-02-15
 */
@Service
public class SysTableServiceImpl implements ISysTableService
{
    @Autowired
    private SysTableMapper sysTableMapper;

    @Autowired
    private ISysDeptService deptService;

    @Autowired
    private ISysUserService userService;

    @Autowired
    private ISysFieldsService sysFieldsService;

    @Autowired
    private ArTableRelationMapper arTableRelationMapper;

    /**
     * 查询档案设置
     *
     * @param recNo 档案设置ID
     * @return 档案设置
     */
    @Override
    public SysTable selectSysTableById(String recNo)
    {
        return sysTableMapper.selectSysTableById(recNo);
    }

    /**
     * 查询档案设置列表
     *
     * @param sysTable 档案设置
     * @return 档案设置
     */
    @Override
    public List<SysTable> selectSysTableList(SysTable sysTable)
    {
        return sysTableMapper.selectSysTableList(sysTable);
    }
    @Override
    public List<SysTable> nonTemporary(SysTable sysTable)
    {
        return sysTableMapper.selectSysTableList(sysTable);
    }

    /**
     * 新增档案设置
     *
     * @param sysTable 档案设置
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int insertSysTable(SysTable sysTable)
    {
        //获取到用户的名字和id
        SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
        SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
        sysTable.setCreateBy(sysDept.getDeptName());
        sysTable.setCreateTime(DateUtils.getNowDate());
        //给新增的信息创建id
        String id = UUid();
        sysTable.setRecNo(id);
        //新增字段表
        SysFields sysFields = new SysFields();
        sysFields.setTableNo(id);
        sysFieldsService.insertSysFieldsByTable(sysFields);
        //创建建表sql
        // 获取该数组对象
        String creatsql = "CREATE TABLE "+sysTable.getTableName()+"( "
                + " id int(4) not null ,";
        List<ChinaPersons.Person> list = ChinaPersons.getPersons();
        for (ChinaPersons.Person person : list) {
            String sql= person.getName()+" "+person.getType()+" not null ,";
            creatsql=creatsql+sql;
        }
        String sql2 = " primary key (id)" + " ) charset=utf8; ";
        creatsql=creatsql+sql2;
        //调用建表方法执行sql 0代表新建表
        crudTable(creatsql,sysTable.getTableName(),0);
        //改属性是设置是临时表或正式表  （“1”：正式表 “0”：临时表 ）
        sysTable.setTableStatue("1");
        //添加新建表的信息到sysTable表里
        sysTableMapper.insertSysTable(sysTable);
        //建表
        String id2 = UUid();
        sysTable.setRecNo(id2);
        //新增字段表
        SysFields sysField = new SysFields();
        sysField.setTableNo(id2);
        sysFieldsService.insertSysFieldsByTable(sysField);
        //创建建临时表sql
        String creatsql2 = "CREATE TABLE "+sysTable.getTableName()+"_tem( "
                + " id int(4) not null ,";
        for (ChinaPersons.Person person : list) {
            String sql= person.getName()+" "+person.getType()+" not null ,";
            creatsql2=creatsql2+sql;
        }
        String sql3 = " primary key (id)"
                + " ) charset=utf8; ";
        creatsql2=creatsql2+sql3;
        //调用建表方法执行sql 0代表新建表
        crudTable(creatsql2,sysTable.getTableName()+"_tem",0);
        sysTable.setTableStatue("0");
        //添加新建临时表的信息到sysTable表里
        sysTable.setTableName(sysTable.getTableName()+"_tem");
        return sysTableMapper.insertSysTable(sysTable);
    }

    /**
     * 修改档案设置
     *
     * @param sysTable 档案设置
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult updateSysTable(SysTable sysTable)
    {
        //获取用户名字和id
        SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
        SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
        sysTable.setUpdateBy(sysDept.getDeptName());
        sysTable.setUpdateTime(DateUtils.getNowDate());
        //拿到前端需要该的表,的表名
        String tableName = sysTableMapper.selectSysTableById(sysTable.getRecNo()).getTableName();
        //创建该表名sql
        String sql = "rename table "+tableName+" to "+sysTable.getTableName()+";";
        //掉用改表名方法 1代表对表修改
        crudTable(sql,tableName,1);
        sysTableMapper.updateSysTable(sysTable);
        // 如果是正式表，临时表也要同时修改
        if (!StringUtils.checkTableName(tableName)) {
            //创建该表名sql
            String sql2 = "rename table " + tableName + "_tem to " + sysTable.getTableName() + "_tem;";
            ///掉用改表名方法 1代表对表修改
            crudTable(sql2, tableName, 1);
            String recNo = sysTableMapper.selectSysTableByName(tableName + "_tem").getRecNo();
            sysTable.setRecNo(recNo);
            sysTable.setTableName(sysTable.getTableName() + "_tem");
            sysTable.setTableStatue("0");
            sysTableMapper.updateSysTable(sysTable);
        }
        return AjaxResult.success();
    }

    /**
     * 批量删除档案设置
     *
     * @param recNos 需要删除的档案设置ID
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int deleteSysTableByIds(String[] recNos)
    {
        int i=0;
        for (String recNo : recNos) {
            SysTable sysTable = new SysTable();
            sysTable.setRecNo(recNo);
            //获取用户名字和id
            SysUser sysUser = userService.selectUserByUserName(SecurityUtils.getUsername());
            SysDept sysDept = deptService.selectDeptById(Long.parseLong(sysUser.getDeptId()));
            sysTable.setUpdateBy(sysDept.getDeptName());
            sysTable.setUpdateTime(DateUtils.getNowDate());
            //拿到需要删除的表名
            String tableName = sysTableMapper.selectSysTableById(recNo).getTableName();
            //创建删表sql
            String sql = "drop table "+tableName+";";
            //掉用改表名方法 1代表对表修改
            crudTable(sql,tableName,1);
            //sysTable的表记录
            sysTableMapper.deleteSysTableById(sysTable);
            if (StringUtils.checkTableName(tableName)){
                //创建删临时表sql
                String sql2 = "drop table "+tableName+"_tem;";
                //掉用改表名方法 1代表对表修改
                crudTable(sql2,tableName+"_tem",1);
                //创建该表的临时表对象
                SysTable sysTable1 = new SysTable();
                sysTable1.setTableName(tableName+"_tem");
                //sysTable的表记录
                sysTable.setRecNo(sysTableMapper.selectSysTableList(sysTable1).get(0).getRecNo());
                sysTableMapper.deleteSysTableById(sysTable);
            }
            // 删除表关系
            arTableRelationMapper.deleteArTableRelationByPOCId(recNo);
            i++;
        }
        return i;
    }

    /**
     * 删除档案设置信息
     *
     * @param sysTable 档案设置ID
     * @return 结果
     */
    @Override
    public int deleteSysTableById(SysTable sysTable)
    {
        return sysTableMapper.deleteSysTableById(sysTable);
    }

    @Override
    public List<SysTable> selectSysTableNameList(SysTable sysTable) {
        return sysTableMapper.selectSysTableNameList(sysTable);
    }

    @Override
    public List<SysTable> selectSysTableByNames(List<String> tableNos) {
        List<SysTable> sysTableList = new ArrayList<>();
        for (String tableNo : tableNos) {
            SysTable sysTable = sysTableMapper.selectSysTableById(tableNo);
            sysTableList.add(sysTable);
        }
        return sysTableList;
    }

    //数据库连接
    private Boolean crudTable(String sql,String title,int i){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://192.168.100.9:3306/archives?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            Statement stat = conn.createStatement();
            //获取数据库表名
            ResultSet rs = conn.getMetaData().getTables(null, null, title, null);
            // 判断表是否存在，如果存在则什么都不做，否则创建表
            if(!rs.next() ){
                stat.executeLargeUpdate(sql);
            }
            //判断是否是删表操作
            if (i==1){
                if(!rs.next()){
                    stat.executeLargeUpdate(sql);
                }
            }
            stat.close();
            conn.close();
            return true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }

    }
    //去掉uuid的 ”-“
    public String UUid(){
        UUID uuid=UUID.randomUUID();
        String[] idd=uuid.toString().split("-");
        String id=idd[0]+idd[1]+idd[2]+idd[3]+ idd[4];
        return id;
    }

}
